<!DOCTYPE html><html><head><title>MySQL学习笔记（Day019：磁盘测试）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'><style>

.note-content  {font-family: 'Helvetica Neue', Arial, 'Hiragino Sans GB', STHeiti, 'Microsoft YaHei', 'WenQuanYi Micro Hei', SimSun, Song, sans-serif;}
</style></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-7177" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day019磁盘测试">MySQL学习笔记（Day019：磁盘测试）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day019磁盘测试">MySQL学习笔记（Day019：磁盘测试）</a><ul>
<li><a href="#一-磁盘调度算法介绍">一. 磁盘调度算法介绍</a><ul>
<li><a href="#1-cfq">1. CFQ</a></li>
<li><a href="#2-deadline">2. Deadline</a></li>
<li><a href="#3-noop">3. Noop</a></li>
</ul>
</li>
<li><a href="#二-iostat下">二. iostat（下）</a></li>
<li><a href="#三-mysql的io使用情况">三. MySQL的IO使用情况</a><ul>
<li><a href="#1-iotop">1. iotop</a></li>
<li><a href="#2-performanceschemathreads">2. performance_schema.threads</a></li>
<li><a href="#3-存储结构对应关系">3. 存储结构对应关系</a></li>
<li><a href="#4-odirect">4. O_DIRECT</a></li>
</ul>
</li>
<li><a href="#四-sysbench">四. sysbench</a><ul>
<li><a href="#1-安装">1. 安装</a></li>
<li><a href="#3-测试">3. 测试</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
</div></div><div id="wmd-preview-section-3" class="wmd-preview-section preview-content">

<h2 id="一-磁盘调度算法介绍">一. 磁盘调度算法介绍</h2>

</div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<h3 id="1-cfq">1. CFQ</h3>

<blockquote>
  <p>CFQ把I/O请求<code>按照进程</code>分别放入进程对应的队列中，所以A进程和B进程发出的I/O请求会在两个队列中。而各个队列内部仍然采用<code>合并和排序</code>的方法，区别仅在于，每一个提交I/O请求的进程都有自己的I/O队列。 <br>
  CFQ的“公平”是针对进程而言的，它以时间片算法为前提，轮转调度队列，默认从当前队列中取4个请求处理，然后处理下一个队列的4个请求。这样就可以确保每个进程享有的I/O资源是均衡的。 <br>
  CFQ的缺点是先来的IO请求不一定能被及时满足，可能出现<code>饥饿</code>的情况。 <br>
  <a href="https://en.wikipedia.org/wiki/CFQ" target="_blank">CFQ Wiki</a></p>
</blockquote>

</div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<h3 id="2-deadline">2. Deadline</h3>

<blockquote>
  <p>同CFQ一样，除了维护一个拥有合并和排序功能的请求队列以外，还额外维护了两个队列，分别是<code>读请求队列</code>和<code>写请求队列</code>，它们都是<code>带有超时的FIFO队列</code>。当新来一个I/O请求时，会被同时插入普通队列和读/写队列，然后处理普通队列中的请求。当调度器发现读/写请求队列中的请求超时的时候，会优先处理这些请求，保证尽可能不产生请求饥饿 <br>
  在DeadLine算法中，每个I/O请求都有一个超时时间，默认<code>读请求是500ms</code>，<code>写请求是5s</code>。 <br>
  <a href="https://en.wikipedia.org/wiki/Deadline_scheduler" target="_blank">Deadline Wiki</a></p>
</blockquote>

</div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<h3 id="3-noop">3. Noop</h3>

<blockquote>
  <p>Noop做的事情非常简单，它不会对I/O请求排序也不会进行任何其它优化（除了合并）。Noop除了对请求合并以外，不再进行任何处理，直接以类似FIFO的顺序提交I/O请求。 <br>
  Noop面向的不是普通的块设备，而是随机访问设备（例如SSD），对于这种设备，不存在传统的寻道时间，那么就没有必要去做那些多余的为了减少寻道时间而采取的事情了。 <br>
  <a href="https://en.wikipedia.org/wiki/Noop_scheduler" target="_blank">Noop Wiki</a></p>
</blockquote>

<hr>

</div><div id="wmd-preview-section-7" class="wmd-preview-section preview-content">

<h2 id="二-iostat下">二. iostat（下）</h2>

<ul><li><p><strong>rrqm/s 和 wrqm/s</strong></p>

<ul>
<li><code>Merge</code>将若干个连续地址的IO请求进行合并。来提高IO的效率</li>
<li><code>rrqm/s</code>是每秒读（read）请求合并的次数</li>
<li><code>wrqm/s</code>是每秒写（write）请求合并的次数</li></ul></li>
<li><p><strong>r/s和w/s</strong></p>

<ul>
<li>在<code>合并之后（after merge）</code> IO请求的次数</li>
<li><code>r/s</code>合并之后每秒读IO的次数</li>
<li><code>w/s</code>合并之后每秒写IO的次数</li>
<li><strong><code>r/s + w/s = IOPS</code></strong></li></ul></li>
<li><p><strong>rsec/s（rKB/s、rMB/s）和 wsec/s（wKB/s、wMB/s）</strong></p>

<ul>
<li><code>sec</code>是<code>Sector（扇区）</code>，为<code>512Byte</code></li>
<li><code>KB</code> 和 <code>MB</code> 是通过扇区的<code>512Byte</code>进行的换算</li></ul></li>
<li><p><strong>avgrq-sz</strong></p>

<ul>
<li>一块磁盘可能存储数据的同时还存储日志，所以请求的IO大小是不一样的</li>
<li>该参数就是平均的请求数，注意，该值需要 * 512Byte 才是最终的结果，因为该值是以扇区为单位的</li></ul></li>
<li><p><strong>avgqu-sz</strong></p>

<ul>
<li>请求的IO队列的平均长度<code>（比较重要）</code></li>
<li>HDD可能在4左右，SSD可以达到30左右</li></ul></li>
<li><p><strong>await、r_await、w_await</strong></p>

<ul>
<li>IO请求平均等待的时间，单位是ms</li>
<li><code>r_await</code>和<code>w_await</code>分别对应<code>读IO请求的等待</code>和<code>写IO请求的等待</code></li></ul></li>
<li><p><strong>svctm</strong></p>

<ul>
<li>服务于IO请求的平均时间</li>
<li>man文档中提示不要相信该值，以后会被移除</li></ul></li>
<li><p><strong>%util</strong></p>

<ul>
<li>磁盘是否空闲；不能简单的等同于IO的使用率；该值可以解释为磁盘是否繁忙</li>
<li>如果该值100% 不能简单的等同于磁盘的负载满了，达到了瓶颈</li>
<li>需要综合<code>avgqu-sz</code>、<code>await</code>等其他指标进行综合判断磁盘是否达到瓶颈</li></ul></li>
</ul>

<hr>

</div><div id="wmd-preview-section-8" class="wmd-preview-section preview-content">

<h2 id="三-mysql的io使用情况">三. MySQL的IO使用情况</h2>

</div><div id="wmd-preview-section-9" class="wmd-preview-section preview-content">

<h3 id="1-iotop">1. iotop</h3>

</div><div id="wmd-preview-section-10" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment line-number">1.</span>shell&gt; iotop -u mysql  <span class="hljs-comment"># -u 表示监控哪个user的进程，所以前提是你的mysql服务是用mysql用户启动的 </span><br></code></pre>

<blockquote>
  <p><strong>注意：</strong> <br>
  上述命令只能看到MySQL的线程ID（Thread ID）</p>
</blockquote>

</div><div id="wmd-preview-section-11" class="wmd-preview-section preview-content">

<h3 id="2-performanceschemathreads">2. performance_schema.threads</h3>

</div><div id="wmd-preview-section-12" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">use</span> performance_schema;</span><br><span class="hljs-comment line-number">2.</span>Database changed<br><span class="hljs-comment line-number">3.</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; desc threads;<br><span class="hljs-comment line-number">5.</span>+<span class="hljs-comment">---------------------+---------------------+------+-----+---------+-------+</span><br><span class="hljs-comment line-number">6.</span>| Field               | Type                | Null | Key | Default | Extra |<br><span class="hljs-comment line-number">7.</span>+<span class="hljs-comment">---------------------+---------------------+------+-----+---------+-------+</span><br><span class="hljs-comment line-number">8.</span>| THREAD_ID           | bigint(20) unsigned | NO   |     | NULL    |       | <span class="hljs-comment">-- MySQL内部线程ID</span><br><span class="hljs-comment line-number">9.</span>| NAME                | varchar(128)        | NO   |     | NULL    |       |<br><span class="hljs-comment line-number">10.</span>| TYPE                | varchar(10)         | NO   |     | NULL    |       |<br><span class="hljs-comment line-number">11.</span>| PROCESSLIST_ID      | bigint(20) unsigned | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">12.</span>| PROCESSLIST_USER    | varchar(32)         | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">13.</span>| PROCESSLIST_HOST    | varchar(60)         | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">14.</span>| PROCESSLIST_DB      | varchar(64)         | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">15.</span>| PROCESSLIST_COMMAND | varchar(16)         | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">16.</span>| PROCESSLIST_TIME    | bigint(20)          | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">17.</span>| PROCESSLIST_STATE   | varchar(64)         | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">18.</span>| PROCESSLIST_INFO    | longtext            | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">19.</span>| PARENT_THREAD_ID    | bigint(20) unsigned | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">20.</span>| ROLE                | varchar(64)         | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">21.</span>| INSTRUMENTED        | enum('YES','NO')    | NO   |     | NULL    |       |<br><span class="hljs-comment line-number">22.</span>| HISTORY             | enum('YES','NO')    | NO   |     | NULL    |       |<br><span class="hljs-comment line-number">23.</span>| CONNECTION_TYPE     | varchar(16)         | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">24.</span>| THREAD_OS_ID        | bigint(20) unsigned | YES  |     | NULL    |       | <span class="hljs-comment">-- 操作系统的线程ID</span><br><span class="hljs-comment line-number">25.</span>+<span class="hljs-comment">---------------------+---------------------+------+-----+---------+-------+</span><br><span class="hljs-comment line-number">26.</span>17 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">27.</span><br><span class="hljs-comment line-number">28.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>,<span class="hljs-keyword">type</span>,thread_id,thread_os_id <span class="hljs-keyword">from</span> threads;</span><br><span class="hljs-comment line-number">29.</span>+<span class="hljs-comment">----------------------------------------+------------+-----------+--------------+</span><br><span class="hljs-comment line-number">30.</span>| name                                   | type       | thread_id | thread_os_id |<br><span class="hljs-comment line-number">31.</span>+<span class="hljs-comment">----------------------------------------+------------+-----------+--------------+</span><br><span class="hljs-comment line-number">32.</span>| thread/sql/main                        | BACKGROUND |         1 |         2481 |<br><span class="hljs-comment line-number">33.</span>| thread/sql/thread_timer_notifier       | BACKGROUND |         2 |         2482 |<br><span class="hljs-comment line-number">34.</span>| thread/innodb/io_read_thread           | BACKGROUND |         3 |         2486 |<br><span class="hljs-comment line-number">35.</span>| thread/innodb/io_read_thread           | BACKGROUND |         4 |         2487 |<br><span class="hljs-comment line-number">36.</span>| thread/innodb/io_read_thread           | BACKGROUND |         5 |         2488 |<br><span class="hljs-comment line-number">37.</span>| thread/innodb/io_write_thread          | BACKGROUND |         6 |         2489 |<br><span class="hljs-comment line-number">38.</span>| thread/innodb/io_write_thread          | BACKGROUND |         7 |         2490 |<br><span class="hljs-comment line-number">39.</span>| thread/innodb/io_write_thread          | BACKGROUND |         8 |         2491 |<br><span class="hljs-comment line-number">40.</span>| thread/innodb/io_write_thread          | BACKGROUND |         9 |         2492 |<br><span class="hljs-comment line-number">41.</span>| thread/innodb/page_cleaner_thread      | BACKGROUND |        10 |         2493 |<br><span class="hljs-comment line-number">42.</span>| thread/innodb/io_read_thread           | BACKGROUND |        11 |         2485 |<br><span class="hljs-comment line-number">43.</span>| thread/innodb/io_log_thread            | BACKGROUND |        12 |         2484 |<br><span class="hljs-comment line-number">44.</span>| thread/innodb/io_ibuf_thread           | BACKGROUND |        13 |         2483 |<br><span class="hljs-comment line-number">45.</span>| thread/innodb/srv_master_thread        | BACKGROUND |        15 |         2501 |  <span class="hljs-comment">-- 主线程</span><br><span class="hljs-comment line-number">46.</span>| thread/sql/background                  | BACKGROUND |        16 |         2502 |<br><span class="hljs-comment line-number">47.</span>| thread/innodb/srv_purge_thread         | BACKGROUND |        17 |         2502 |<br><span class="hljs-comment line-number">48.</span>| thread/sql/background                  | BACKGROUND |        18 |         2503 |<br><span class="hljs-comment line-number">49.</span>| thread/innodb/srv_monitor_thread       | BACKGROUND |        19 |         2500 |<br><span class="hljs-comment line-number">50.</span>| thread/innodb/srv_error_monitor_thread | BACKGROUND |        20 |         2499 |<br><span class="hljs-comment line-number">51.</span>| thread/sql/background                  | BACKGROUND |        21 |         2504 |<br><span class="hljs-comment line-number">52.</span>| thread/sql/background                  | BACKGROUND |        22 |         2505 |<br><span class="hljs-comment line-number">53.</span>| thread/innodb/srv_lock_timeout_thread  | BACKGROUND |        23 |         2498 |<br><span class="hljs-comment line-number">54.</span>| thread/innodb/dict_stats_thread        | BACKGROUND |        24 |         2507 |<br><span class="hljs-comment line-number">55.</span>| thread/innodb/buf_dump_thread          | BACKGROUND |        25 |         2506 |<br><span class="hljs-comment line-number">56.</span>| thread/sql/signal_handler              | BACKGROUND |        26 |         2510 |<br><span class="hljs-comment line-number">57.</span>| thread/sql/compress_gtid_table         | FOREGROUND |        27 |         2511 |<br><span class="hljs-comment line-number">58.</span>| thread/sql/one_connection              | FOREGROUND |        28 |         2514 |  <span class="hljs-comment">-- FOREGROUND前台线程</span><br><span class="hljs-comment line-number">59.</span>+<span class="hljs-comment">----------------------------------------+------------+-----------+--------------+</span><br><span class="hljs-comment line-number">60.</span>27 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">61.</span><br><span class="hljs-comment line-number">62.</span><span class="hljs-comment">-- thread/sql/one_connection 就是我连接的线程</span><br><span class="hljs-comment line-number">63.</span><br><span class="hljs-comment line-number">64.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">name</span>,thread_id,thread_os_id,processlist_id <span class="hljs-keyword">from</span> threads;</span>  <span class="hljs-comment">-- 查看processlist_id</span><br><span class="hljs-comment line-number">65.</span>+<span class="hljs-comment">----------------------------------------+-----------+--------------+----------------+</span><br><span class="hljs-comment line-number">66.</span>| name                                   | thread_id | thread_os_id | processlist_id |<br><span class="hljs-comment line-number">67.</span>+<span class="hljs-comment">----------------------------------------+-----------+--------------+----------------+</span><br><span class="hljs-comment line-number">68.</span>| thread/sql/main                        |         1 |         2481 |           NULL |<br><span class="hljs-comment line-number">69.</span>| thread/sql/thread_timer_notifier       |         2 |         2482 |           NULL |<br><span class="hljs-comment line-number">70.</span>| thread/innodb/io_read_thread           |         3 |         2486 |           NULL |<br><span class="hljs-comment line-number">71.</span>| thread/innodb/io_read_thread           |         4 |         2487 |           NULL |<br><span class="hljs-comment line-number">72.</span>| thread/innodb/io_read_thread           |         5 |         2488 |           NULL |<br><span class="hljs-comment line-number">73.</span>| thread/innodb/io_write_thread          |         6 |         2489 |           NULL |<br><span class="hljs-comment line-number">74.</span>| thread/innodb/io_write_thread          |         7 |         2490 |           NULL |<br><span class="hljs-comment line-number">75.</span>| thread/innodb/io_write_thread          |         8 |         2491 |           NULL |<br><span class="hljs-comment line-number">76.</span>| thread/innodb/io_write_thread          |         9 |         2492 |           NULL |<br><span class="hljs-comment line-number">77.</span>| thread/innodb/page_cleaner_thread      |        10 |         2493 |           NULL |<br><span class="hljs-comment line-number">78.</span>| thread/innodb/io_read_thread           |        11 |         2485 |           NULL |<br><span class="hljs-comment line-number">79.</span>| thread/innodb/io_log_thread            |        12 |         2484 |           NULL |<br><span class="hljs-comment line-number">80.</span>| thread/innodb/io_ibuf_thread           |        13 |         2483 |           NULL |<br><span class="hljs-comment line-number">81.</span>| thread/innodb/srv_master_thread        |        15 |         2501 |           NULL |<br><span class="hljs-comment line-number">82.</span>| thread/sql/background                  |        16 |         2502 |           NULL |<br><span class="hljs-comment line-number">83.</span>| thread/innodb/srv_purge_thread         |        17 |         2502 |           NULL |<br><span class="hljs-comment line-number">84.</span>| thread/sql/background                  |        18 |         2503 |           NULL |<br><span class="hljs-comment line-number">85.</span>| thread/innodb/srv_monitor_thread       |        19 |         2500 |           NULL |<br><span class="hljs-comment line-number">86.</span>| thread/innodb/srv_error_monitor_thread |        20 |         2499 |           NULL |<br><span class="hljs-comment line-number">87.</span>| thread/sql/background                  |        21 |         2504 |           NULL |<br><span class="hljs-comment line-number">88.</span>| thread/sql/background                  |        22 |         2505 |           NULL |<br><span class="hljs-comment line-number">89.</span>| thread/innodb/srv_lock_timeout_thread  |        23 |         2498 |           NULL |<br><span class="hljs-comment line-number">90.</span>| thread/innodb/dict_stats_thread        |        24 |         2507 |           NULL |<br><span class="hljs-comment line-number">91.</span>| thread/innodb/buf_dump_thread          |        25 |         2506 |           NULL |<br><span class="hljs-comment line-number">92.</span>| thread/sql/signal_handler              |        26 |         2510 |           NULL |<br><span class="hljs-comment line-number">93.</span>| thread/sql/compress_gtid_table         |        27 |         2511 |              1 |<br><span class="hljs-comment line-number">94.</span>| thread/sql/one_connection              |        28 |         2514 |              2 |<br><span class="hljs-comment line-number">95.</span>+<span class="hljs-comment">----------------------------------------+-----------+--------------+----------------+</span><br><span class="hljs-comment line-number">96.</span>27 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">97.</span><br><span class="hljs-comment line-number">98.</span><span class="hljs-comment">-- processlist_id 对应的就是 show processlist中的id</span><br><span class="hljs-comment line-number">99.</span>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">processlist</span>;</span> <br><span class="hljs-comment line-number">100.</span>+<span class="hljs-comment">----+------+-----------+--------------------+---------+------+----------+------------------+</span><br><span class="hljs-comment line-number">101.</span>| Id | User | Host      | db                 | Command | Time | State    | Info             |<br><span class="hljs-comment line-number">102.</span>+<span class="hljs-comment">----+------+-----------+--------------------+---------+------+----------+------------------+</span><br><span class="hljs-comment line-number">103.</span>|  2 | root | localhost | performance_schema | Query   |    0 | starting | <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">processlist</span> |<br><span class="hljs-comment line-number">104.</span>+<span class="hljs-comment">----+------+-----------+--------------------+---------+------+----------+------------------+</span><br><span class="hljs-comment line-number">105.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">106.</span><br><span class="hljs-comment line-number">107.</span>mysql&gt; <span class="hljs-keyword">select</span> connection_id();</span>   <span class="hljs-comment">-- 查看当前connection的id</span><br><span class="hljs-comment line-number">108.</span>+<span class="hljs-comment">-----------------+</span><br><span class="hljs-comment line-number">109.</span>| connection_id() |<br><span class="hljs-comment line-number">110.</span>+<span class="hljs-comment">-----------------+</span><br><span class="hljs-comment line-number">111.</span>|               2 |<br><span class="hljs-comment line-number">112.</span>+<span class="hljs-comment">-----------------+</span><br><span class="hljs-comment line-number">113.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p><strong>通过<code>threads表</code>中的信息，结合<code>iotop -u mysql</code>的输出，就可以知道某个线程的io使用情况</strong></p>
  
  <p>MySQL 5.6 版本中没有<code>thread_os_id</code>这个列。 <br>
  作业一：如何将iotop中的Thread ID和MySQL5.6中的threads表中的信息对应起来。</p>
</blockquote>

</div><div id="wmd-preview-section-13" class="wmd-preview-section preview-content">

<h3 id="3-存储结构对应关系">3. 存储结构对应关系</h3>

</div><div id="wmd-preview-section-14" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment line-number">1.</span>                  +-------------+-------------+-------------+<br><span class="hljs-comment line-number">2.</span>      Database    |     <span class="hljs-number">16</span>K     |     <span class="hljs-number">16</span>K     |     <span class="hljs-number">16</span>K     |<br><span class="hljs-comment line-number">3.</span>                  +------+------+-------------+-------------+<br><span class="hljs-comment line-number">4.</span>                         |<br><span class="hljs-comment line-number">5.</span>+------------------------------------------------------------------------+<br><span class="hljs-comment line-number">6.</span>                         |<br><span class="hljs-comment line-number">7.</span>                         +------+<br><span class="hljs-comment line-number">8.</span>                                |<br><span class="hljs-comment line-number">9.</span>                  +------+------v------+------+<br><span class="hljs-comment line-number">10.</span>     Filesystem   |  <span class="hljs-number">4</span>K  |  <span class="hljs-number">4</span>K  |  <span class="hljs-number">4</span>K  |  <span class="hljs-number">4</span>K  |<br><span class="hljs-comment line-number">11.</span>                  +---+--+------+------+------+<br><span class="hljs-comment line-number">12.</span>                      |<br><span class="hljs-comment line-number">13.</span>+------------------------------------------------------------------------+<br><span class="hljs-comment line-number">14.</span>                      |<br><span class="hljs-comment line-number">15.</span>                      +--+<br><span class="hljs-comment line-number">16.</span>                         |<br><span class="hljs-comment line-number">17.</span>                         v<br><span class="hljs-comment line-number">18.</span>                  +------+------+          +------+<br><span class="hljs-comment line-number">19.</span>        Disk      | <span class="hljs-number">512</span>B | <span class="hljs-number">512</span>B | ...  ... | <span class="hljs-number">512</span>B |<br><span class="hljs-comment line-number">20.</span>                  +------+------+          +------+<br></code></pre>

<blockquote>
  <p><strong>SSD扇区的大小一般为4K或者8K。但是为了兼容HDD，SSD通过Flash Translation Layer (FTL)的方式转换成512B</strong></p>
</blockquote>

</div><div id="wmd-preview-section-15" class="wmd-preview-section preview-content">

<h3 id="4-odirect">4. O_DIRECT</h3>

<ul><li><p><strong>fwrite / fsync</strong></p>

<ul>
<li><code>fwrite</code>是把数据写入文件系统层（Filesystem）（可能有cache），并不能保证写入Disk</li>
<li><code>fsync</code>可以保证把数据写入到Disk（数据落盘）</li></ul>

<blockquote>
  <p>只通过<code>fwrite</code>写入数据特别快（因为有缓存），但随后调用<code>fsync</code>就会很慢，这个速度取决于磁盘的<code>IOPS</code> <br>
  如果不手工执行<code>fysnc</code>，当Filesystem的<code>cache</code>小于<code>10%</code>时，操作系统才会将数据刷入磁盘。所以可能存在数据丢失的风险，比如掉电</p>
</blockquote></li>
<li><p><strong>O_DIRECT</strong></p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment line-number">1.</span>+-------------------+               +-------------------+              +-------------------+<br><span class="hljs-comment line-number">2.</span>|                   |    fwrite     |                   |    fsync     |                   |<br><span class="hljs-comment line-number">3.</span>|    Buffer Pool    +---------------&gt; Filesystem Cache  +--------------&gt;       Disk        |<br><span class="hljs-comment line-number">4.</span>|                   |               |                   |              |                   |<br><span class="hljs-comment line-number">5.</span>+--------+----------+               +-------------------+              +---------+---------+<br><span class="hljs-comment line-number">6.</span>         |                                                                       ^<br><span class="hljs-comment line-number">7.</span>         |                                                                       |<br><span class="hljs-comment line-number">8.</span>         |                   innodb_flush_method = O_DIRECT                      |<br><span class="hljs-comment line-number">9.</span>         +-----------------------------------------------------------------------+<br></code></pre>

<blockquote>
  <p><strong><code>O_DIRECT</code>的设置参数是告诉系统<code>直接将数据写入磁盘</code>，跳过文件系统的缓存。等同于使用<code>裸设备</code>的效果</strong></p>
</blockquote></li>
</ul>

<hr>

</div><div id="wmd-preview-section-16" class="wmd-preview-section preview-content">

<h2 id="四-sysbench">四. sysbench</h2>

</div><div id="wmd-preview-section-17" class="wmd-preview-section preview-content">

<h3 id="1-安装">1. 安装</h3>

<p>建议安装<code>sysbenh-0.5</code>的版本</p>

</div><div id="wmd-preview-section-36" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment line-number">1.</span>shell&gt; https://github.com/akopytov/sysbench.git <span class="hljs-comment"># 通过git clone得到源码</span><br><span class="hljs-comment line-number">2.</span>shell&gt; <span class="hljs-built_in">cd</span> sysbench<br><span class="hljs-comment line-number">3.</span>shell&gt; ./autogen.sh<br><span class="hljs-comment line-number">4.</span>shell&gt; ./configure  --with-mysql-includes=/usr/<span class="hljs-built_in">local</span>/mysql56/include/  --with-mysql-libs=/usr/<span class="hljs-built_in">local</span>/mysql56/lib/  <span class="hljs-comment"># 关联mysql的头文件和库</span><br><span class="hljs-comment line-number">5.</span><span class="hljs-comment">##</span><br><span class="hljs-comment line-number">6.</span><span class="hljs-comment">## 注意，如果我这里使用mysql5.7.9 的include和lib ，提示我 /usr/bin/ld: cannot find -lmysqlclient_r</span><br><span class="hljs-comment line-number">7.</span><span class="hljs-comment">##</span><br><span class="hljs-comment line-number">8.</span><br><span class="hljs-comment line-number">9.</span>shell&gt; make -j <span class="hljs-number">2</span> <span class="hljs-comment"># -j 2 表示用几个cpu核心进行编译</span><br><span class="hljs-comment line-number">10.</span>shell&gt; make install <span class="hljs-comment"># 默认安装到 /usr/local/bin , 如果有自定义目录，configure增加参数 --prefix=自定义目录</span><br><span class="hljs-comment line-number">11.</span>shell&gt; <span class="hljs-built_in">echo</span> <span class="hljs-string">"export LD_LIBRARY_PATH=/usr/local/mysql56/lib/:<span class="hljs-variable">$LD_LIBRARY_PATH</span>"</span> &gt;&gt; ~/.bashrc <span class="hljs-comment"># 添加LD_LIBRARY_PATH</span><br><span class="hljs-comment line-number">12.</span>shell&gt; <span class="hljs-built_in">source</span> ~/.bashrc<br><span class="hljs-comment line-number">13.</span>shell&gt; sysbench --version<br><span class="hljs-comment line-number">14.</span>sysbench <span class="hljs-number">0.5</span><br></code></pre>

</div><div id="wmd-preview-section-94" class="wmd-preview-section preview-content">

<h3 id="3-测试">3. 测试</h3>

</div><div id="wmd-preview-section-4255" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">#</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment"># 生成测试文件</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">#</span><br><span class="hljs-comment line-number">4.</span>shell&gt; sysbench --test=fileio \               <span class="hljs-comment"># File IO测试</span><br><span class="hljs-comment line-number">5.</span>                --file-num=<span class="hljs-number">4</span> \                <span class="hljs-comment"># 测试文件数是4个</span><br><span class="hljs-comment line-number">6.</span>                --file-block-size=<span class="hljs-number">8</span>K \        <span class="hljs-comment"># block size是8K</span><br><span class="hljs-comment line-number">7.</span>                --file-total-size=<span class="hljs-number">1</span>G \        <span class="hljs-comment"># 4个文件的总大小是1G</span><br><span class="hljs-comment line-number">8.</span>                --file-test-mode=rndrd \       <span class="hljs-comment"># 测试方法是随机读</span><br><span class="hljs-comment line-number">9.</span>                --file-extra-flags=direct \   <span class="hljs-comment"># direct io，跳过缓存</span><br><span class="hljs-comment line-number">10.</span>                --max-requests=<span class="hljs-number">0</span> \            <span class="hljs-comment"># 一共发起多少请求，0表示任意</span><br><span class="hljs-comment line-number">11.</span>                --max-time=<span class="hljs-number">3600</span> \             <span class="hljs-comment"># 测试3600s </span><br><span class="hljs-comment line-number">12.</span>                --num-threads=<span class="hljs-number">4</span> \             <span class="hljs-comment"># 使用4个线程</span><br><span class="hljs-comment line-number">13.</span>                prepare <span class="hljs-comment">#  run or cleanup     # prepare：生成文件</span><br><span class="hljs-comment line-number">14.</span>                                              <span class="hljs-comment"># run：开始测试</span><br><span class="hljs-comment line-number">15.</span>                                              <span class="hljs-comment"># cleanup：删除测试文件</span><br><span class="hljs-comment line-number">16.</span><br><span class="hljs-comment line-number">17.</span><span class="hljs-comment">## 其他说明  sysbench --test=fileio help</span><br><span class="hljs-comment line-number">18.</span><span class="hljs-comment"># --file-num=N                  创建文件数</span><br><span class="hljs-comment line-number">19.</span>  <span class="hljs-comment"># --file-block-size=N           block size大小</span><br><span class="hljs-comment line-number">20.</span>  <span class="hljs-comment"># --file-total-size=SIZE        文件数的大小总和</span><br><span class="hljs-comment line-number">21.</span>  <span class="hljs-comment"># --file-test-mode=STRING       测试模式 {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw} （顺序写，顺序读写，顺序读，随机读，随机写，随机读写）</span><br><span class="hljs-comment line-number">22.</span>  <span class="hljs-comment"># --file-io-mode=STRING         文件操作方式 {sync,async,mmap} </span><br><span class="hljs-comment line-number">23.</span>  <span class="hljs-comment"># --file-extra-flags=STRING     打开文件的额外标志 {sync,dsync,direct} []</span><br><span class="hljs-comment line-number">24.</span>  <span class="hljs-comment"># --file-fsync-freq=N           多少请求后执行fsync。默认是0，不执行</span><br><span class="hljs-comment line-number">25.</span>  <span class="hljs-comment"># --file-fsync-all=[on|off]     是否每次操作后都执行fsync</span><br><span class="hljs-comment line-number">26.</span>  <span class="hljs-comment"># --file-fsync-end=[on|off]     测完成后执行fsync，默认是on</span><br><span class="hljs-comment line-number">27.</span>  <span class="hljs-comment"># --file-fsync-mode=STRING      同步的方法 {fsync, fdatasync}默认是 [fsync]</span><br><span class="hljs-comment line-number">28.</span>  <span class="hljs-comment"># --file-merged-requests=N      最多多少IO请求被合并，默认为0，不合并</span><br><span class="hljs-comment line-number">29.</span>  <span class="hljs-comment"># --file-rw-ratio=N             读写比例默认是 [1.5]，即 3:2</span><br><span class="hljs-comment line-number">30.</span><br></code></pre>

</div><div id="wmd-preview-section-7175" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">#</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment"># 开始测试</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">#</span><br><span class="hljs-comment line-number">4.</span>shell&gt; sysbench --test=fileio \               <br><span class="hljs-comment line-number">5.</span>                --file-num=<span class="hljs-number">4</span> \               <br><span class="hljs-comment line-number">6.</span>                --file-block-size=<span class="hljs-number">8</span>K \       <br><span class="hljs-comment line-number">7.</span>                --file-total-size=<span class="hljs-number">1</span>G \        <br><span class="hljs-comment line-number">8.</span>                --file-test-mode=rndrd \    <br><span class="hljs-comment line-number">9.</span>                --file-extra-flags=direct \   <br><span class="hljs-comment line-number">10.</span>                --max-requests=<span class="hljs-number">0</span> \            <br><span class="hljs-comment line-number">11.</span>                --max-time=<span class="hljs-number">30</span> \             <span class="hljs-comment"># 简单测试，测试30秒</span><br><span class="hljs-comment line-number">12.</span>                --num-threads=<span class="hljs-number">4</span> \             <br><span class="hljs-comment line-number">13.</span>                --report-interval=<span class="hljs-number">3</span> \       <span class="hljs-comment"># 每3秒产生报告</span><br><span class="hljs-comment line-number">14.</span>                run<br><span class="hljs-comment line-number">15.</span>sysbench <span class="hljs-number">0.5</span>:  multi-threaded system evaluation benchmark<br><span class="hljs-comment line-number">16.</span><br><span class="hljs-comment line-number">17.</span>Running the <span class="hljs-built_in">test</span> with following options:<br><span class="hljs-comment line-number">18.</span>Number of threads: <span class="hljs-number">4</span><br><span class="hljs-comment line-number">19.</span>Report intermediate results every <span class="hljs-number">3</span> second(s)<br><span class="hljs-comment line-number">20.</span>Random number generator seed is <span class="hljs-number">0</span> and will be ignored<br><span class="hljs-comment line-number">21.</span><br><span class="hljs-comment line-number">22.</span><br><span class="hljs-comment line-number">23.</span>Extra file open flags: <span class="hljs-number">3</span><br><span class="hljs-comment line-number">24.</span><span class="hljs-number">4</span> files, <span class="hljs-number">256</span>Mb each<br><span class="hljs-comment line-number">25.</span><span class="hljs-number">1</span>Gb total file size<br><span class="hljs-comment line-number">26.</span>Block size <span class="hljs-number">8</span>Kb<br><span class="hljs-comment line-number">27.</span>Number of IO requests: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">28.</span>Read/Write ratio <span class="hljs-keyword">for</span> combined random IO <span class="hljs-built_in">test</span>: <span class="hljs-number">1.50</span><br><span class="hljs-comment line-number">29.</span>Periodic FSYNC enabled, calling fsync() each <span class="hljs-number">100</span> requests.<br><span class="hljs-comment line-number">30.</span>Calling fsync() at the end of <span class="hljs-built_in">test</span>, Enabled.<br><span class="hljs-comment line-number">31.</span>Using synchronous I/O mode<br><span class="hljs-comment line-number">32.</span>Doing random <span class="hljs-built_in">read</span> <span class="hljs-built_in">test</span><br><span class="hljs-comment line-number">33.</span>Threads started!<br><span class="hljs-comment line-number">34.</span><br><span class="hljs-comment line-number">35.</span>[   <span class="hljs-number">3</span>s] reads: <span class="hljs-number">1.70</span> MB/s writes: <span class="hljs-number">0.00</span> MB/s fsyncs: <span class="hljs-number">0.00</span>/s response time: <span class="hljs-number">54.416</span>ms (<span class="hljs-number">95</span>%)<br><span class="hljs-comment line-number">36.</span>[   <span class="hljs-number">6</span>s] reads: <span class="hljs-number">1.78</span> MB/s writes: <span class="hljs-number">0.00</span> MB/s fsyncs: <span class="hljs-number">0.00</span>/s response time: <span class="hljs-number">55.469</span>ms (<span class="hljs-number">95</span>%)<br><span class="hljs-comment line-number">37.</span>[   <span class="hljs-number">9</span>s] reads: <span class="hljs-number">1.75</span> MB/s writes: <span class="hljs-number">0.00</span> MB/s fsyncs: <span class="hljs-number">0.00</span>/s response time: <span class="hljs-number">55.253</span>ms (<span class="hljs-number">95</span>%)<br><span class="hljs-comment line-number">38.</span>[  <span class="hljs-number">12</span>s] reads: <span class="hljs-number">1.66</span> MB/s writes: <span class="hljs-number">0.00</span> MB/s fsyncs: <span class="hljs-number">0.00</span>/s response time: <span class="hljs-number">52.120</span>ms (<span class="hljs-number">95</span>%)<br><span class="hljs-comment line-number">39.</span>[  <span class="hljs-number">15</span>s] reads: <span class="hljs-number">1.76</span> MB/s writes: <span class="hljs-number">0.00</span> MB/s fsyncs: <span class="hljs-number">0.00</span>/s response time: <span class="hljs-number">51.840</span>ms (<span class="hljs-number">95</span>%)<br><span class="hljs-comment line-number">40.</span>[  <span class="hljs-number">18</span>s] reads: <span class="hljs-number">1.79</span> MB/s writes: <span class="hljs-number">0.00</span> MB/s fsyncs: <span class="hljs-number">0.00</span>/s response time: <span class="hljs-number">50.933</span>ms (<span class="hljs-number">95</span>%)<br><span class="hljs-comment line-number">41.</span>[  <span class="hljs-number">21</span>s] reads: <span class="hljs-number">1.78</span> MB/s writes: <span class="hljs-number">0.00</span> MB/s fsyncs: <span class="hljs-number">0.00</span>/s response time: <span class="hljs-number">54.858</span>ms (<span class="hljs-number">95</span>%)<br><span class="hljs-comment line-number">42.</span>[  <span class="hljs-number">24</span>s] reads: <span class="hljs-number">1.88</span> MB/s writes: <span class="hljs-number">0.00</span> MB/s fsyncs: <span class="hljs-number">0.00</span>/s response time: <span class="hljs-number">50.857</span>ms (<span class="hljs-number">95</span>%)<br><span class="hljs-comment line-number">43.</span>[  <span class="hljs-number">27</span>s] reads: <span class="hljs-number">1.75</span> MB/s writes: <span class="hljs-number">0.00</span> MB/s fsyncs: <span class="hljs-number">0.00</span>/s response time: <span class="hljs-number">56.238</span>ms (<span class="hljs-number">95</span>%)<br><span class="hljs-comment line-number">44.</span>[  <span class="hljs-number">30</span>s] reads: <span class="hljs-number">1.61</span> MB/s writes: <span class="hljs-number">0.00</span> MB/s fsyncs: <span class="hljs-number">0.00</span>/s response time: <span class="hljs-number">64.097</span>ms (<span class="hljs-number">95</span>%)<br><span class="hljs-comment line-number">45.</span>Operations performed:  <span class="hljs-number">6709</span> reads, <span class="hljs-number">0</span> writes, <span class="hljs-number">0</span> Other = <span class="hljs-number">6709</span> Total<br><span class="hljs-comment line-number">46.</span>Read <span class="hljs-number">52.414</span>Mb  Written <span class="hljs-number">0</span>b  Total transferred <span class="hljs-number">52.414</span>Mb  (<span class="hljs-number">1.7462</span>Mb/sec)<br><span class="hljs-comment line-number">47.</span>  <span class="hljs-number">223.51</span> Requests/sec executed  <span class="hljs-comment"># 这个就是IOPS</span><br><span class="hljs-comment line-number">48.</span><br><span class="hljs-comment line-number">49.</span>General statistics:<br><span class="hljs-comment line-number">50.</span>    total time:                          <span class="hljs-number">30.0160</span>s<br><span class="hljs-comment line-number">51.</span>    total number of events:              <span class="hljs-number">6709</span><br><span class="hljs-comment line-number">52.</span>    total time taken by event execution: <span class="hljs-number">120.0223</span>s<br><span class="hljs-comment line-number">53.</span>    response time:<br><span class="hljs-comment line-number">54.</span>         min:                                  <span class="hljs-number">0.13</span>ms<br><span class="hljs-comment line-number">55.</span>         avg:                                 <span class="hljs-number">17.89</span>ms<br><span class="hljs-comment line-number">56.</span>         max:                                <span class="hljs-number">254.62</span>ms<br><span class="hljs-comment line-number">57.</span>         approx.  <span class="hljs-number">95</span> percentile:              <span class="hljs-number">54.97</span>ms<br><span class="hljs-comment line-number">58.</span><br><span class="hljs-comment line-number">59.</span>Threads fairness:<br><span class="hljs-comment line-number">60.</span>    events (avg/stddev):           <span class="hljs-number">1677.2500</span>/<span class="hljs-number">28.16</span><br><span class="hljs-comment line-number">61.</span>    execution time (avg/stddev):   <span class="hljs-number">30.0056</span>/<span class="hljs-number">0.01</span><br><span class="hljs-comment line-number">62.</span><br><span class="hljs-comment line-number">63.</span><span class="hljs-comment">##</span><br><span class="hljs-comment line-number">64.</span><span class="hljs-comment">## 上述测试随机读的速度在1.7MB/s左右， </span><br><span class="hljs-comment line-number">65.</span><span class="hljs-comment">## （1.7MB/s * 1024 / 8KB =217）换算后得到的值就是IOPS，约等于上面的223。</span><br><span class="hljs-comment line-number">66.</span><span class="hljs-comment">##</span><br><span class="hljs-comment line-number">67.</span><br></code></pre>

<blockquote>
  <p>测试完成后执行<code>cleanup</code> <br>
  <strong>如果是真实的测试 <code>max-time</code>设置成一周的时间</strong> <br>
  <code>run</code>期间可以使用<code>iotop</code>或者<code>iostat</code>进行观察</p>
</blockquote></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>